--create database FACEBOOK
/*go


USE FACEBOOK


create table SOURCE_ACCOUNT 
(
	sourceId bigint primary key not null,
	sourceName varchar(50) not null,
    sourceSurname varchar(50) not null,
    added datetime
)
go

create table FRIEND
(
        friendId bigint primary key not null,
        friendName varchar(50) not null,
        friendSurname varchar(50) not null,
        friendGender varchar(6),
        friendBirthPlace varchar(50),
        friendCurrentPlace varchar(50),
        friendAge int
)
go

create table FRIENDSHIP
(
		friendshipId int primary key identity not null,
        friendIdA bigint references FRIEND(friendId),
        friendIdB bigint references FRIEND(friendId)
)
go

create table FAMILY_RELATION
(
        familyRelationId int primary key identity(0,1) not null,
        familyRelationName varchar(20) not null
)
go

create table FAMILY
(
		familyId int primary key identity not null,
        friendIdA bigint foreign key references FRIEND(friendId),
        friendIdB bigint foreign key references FRIEND(friendId),
        familyRelationAB int foreign key references FAMILY_RELATION(familyRelationId),
        familyRelationBA int foreign key references FAMILY_RELATION(familyRelationId)
)
go

create table FRIEND_SOURCE
(
	friendId bigint foreign key references FRIEND(friendId),
	sourceId bigint foreign key references SOURCE_ACCOUNT(sourceId)
)

create table FRIENDSHIP_SOURCE
(
	friendshipId int foreign key references FRIENDSHIP(friendshipId),
	sourceId bigint foreign key references SOURCE_ACCOUNT(sourceId)
)
go

create table FAMILY_SOURCE
(
	familyId int foreign key references FAMILY(familyId),
	sourceId bigint foreign key references SOURCE_ACCOUNT(sourceId)
)
go

create table SCHOOL_TYPE
(
        typeId int primary key identity not null,
        typeName varchar(20) not null 
)
go

create table SCHOOL
(
        schoolId bigint primary key not null,
        schoolName varchar(50) not null,
        schoolType int foreign key references SCHOOL_TYPE(typeId)
)
go


create table EDUCATION
(
        educationId bigint primary key identity not null,
        friendId bigint foreign key references FRIEND(friendId),
        schoolId bigint foreign key references SCHOOL(schoolId),
        graduation int
)
go


create table EMPLOYER
(
        employerId bigint primary key not null,
        employerName varchar(50) not null
)
go


create table WORK
(
        workId int primary key identity not null,
        friendId bigint foreign key references FRIEND(friendId),
        employerId bigint foreign key references EMPLOYER(employerId)
)
go*/

drop trigger INSERTING_SOURCE
drop trigger INSERTING_FRIEND
drop trigger FRIENDS_ALREADY
drop trigger FAMILY_ALREADY
go

create trigger INSERTING_SOURCE on SOURCE_ACCOUNT
instead of insert
as
begin
	declare @id bigint,
    @name varchar(50),
    @surname varchar(50),
    @added datetime
        
    select @id = (select sourceId from inserted)
    select @name = (select sourceName from inserted)
    select @surname = (select sourceSurname from inserted)
    select @added = (select added from inserted)
        
    if( not exists (select SA.sourceId from SOURCE_ACCOUNT SA where SA.sourceId = @id))
    begin
		insert into SOURCE_ACCOUNT values(@id, @name, @surname, @added)                 
    end
    else
    begin
		update SOURCE_ACCOUNT set added = @added where sourceId = @id
	end
end
go

create trigger INSERTING_FRIEND on FRIEND
instead of insert
as
begin
	declare @id bigint,
    @name varchar(50),
    @surname varchar(50),
    @gender varchar(6),
    @birthplace varchar(50),
    @currentplace varchar(50),
    @age int,
    @sourceId bigint
        
    select @id = (select friendId from inserted)
    select @name = (select friendName from inserted)
    select @surname = (select friendSurName from inserted)
    select @gender = (select friendGender from inserted)
    select @birthplace = (select friendBirthPlace from inserted)
    select @currentplace = (select friendCurrentPlace from inserted)
    select @age = (select friendAge from inserted)
    select @sourceId = (select sourceId from SOURCE_ACCOUNT where added = (select MAX(added) from SOURCE_ACCOUNT))
        
    if(exists (select F.friendId from FRIEND F where F.friendId = @id))
    begin
		update FRIEND set friendName = @name where (friendId = @id) AND ((friendName is null) OR (friendName = 'NULL'))  AND ((@name  != 'NULL') OR (@name is not null))
        update FRIEND set friendSurname = @surname where (friendId = @id) AND ((friendSurname is null) OR (friendSurname = 'NULL')) AND ((@surname != 'NULL') OR (@surname is not null))                        
        update FRIEND set friendGender = @gender where (friendId = @id) AND ((friendGender is null) OR (friendGender = 'NULL'))  AND ((@gender  != 'NULL') OR (@gender is not null))
        update FRIEND set friendBirthPlace = @birthplace where (friendId = @id) AND ((friendBirthPlace is null) OR (friendBirthPlace = 'NULL')) AND ((@birthplace != 'NULL') OR (@birthplace is not null))
        update FRIEND set friendCurrentPlace = @currentplace where (friendId = @id) AND ((friendCurrentPlace is null) OR (friendCurrentPlace = 'NULL')) AND ((@currentplace != 'NULL') OR (@currentplace is not null))
        update FRIEND set friendAge = @age where (friendId = @id) AND ((friendAge is null) OR (friendAge= 0)) AND ((@age != 0) OR (@age is not null))                   
    end
    else
		begin
			insert into FRIEND values(@id, @name,@surname, @gender, @birthplace, @currentplace, @age)
        end
     if(not(exists(select * from FRIEND_SOURCE where friendId = @id and sourceId = @sourceId)))
     begin
		insert into FRIEND_SOURCE values(@id,@sourceId)
	end
end
go


/*create trigger DELETING_FRIEND on FRIEND
instead of delete
as
begin
        declare @id bigint
        select @id = (select friendId from deleted)
        delete from FRIENDSHIP where (friendIdA = @id) OR (friendIdB = @id)
        delete from FAMILY where (friendIdA = @id) OR (friendIdB = @id)
        delete from EDUCATION where friendId = @id
        delete from WORK where friendId = @id
        delete from FRIEND where friendId = @id
end
go*/


create trigger FRIENDS_ALREADY on FRIENDSHIP
instead of insert
as
begin
        declare @idA bigint, @idB bigint, @sourceid bigint
        select @idA = (select friendIdA from inserted)
        select @idB = (select friendIdB from inserted)
        select @sourceid = (select sourceId from SOURCE_ACCOUNT where added = (select MAX(added) from SOURCE_ACCOUNT))
        if(not exists(select * from FRIENDSHIP F where ((F.friendIdA = @idA) AND (F.friendIdB = @idB)) OR ((F.friendIdA = @idB) AND (F.friendIdB = @idA))))
        begin
			insert into FRIENDSHIP values(@idA, @idB)
			insert into FRIENDSHIP_SOURCE values ((select MAX(friendshipId) from FRIENDSHIP), @sourceid)
        end
        else
        begin
			declare @friendshipid int = (select friendshipId from FRIENDSHIP F where ((F.friendIdA = @idA) AND (F.friendIdB = @idB)) OR ((F.friendIdA = @idB) AND (F.friendIdB = @idA)))
			if(not exists(select * from FRIENDSHIP_SOURCE where friendshipId = @friendshipid and sourceId = @sourceid))
			begin
				insert into FRIENDSHIP_SOURCE values(@friendshipid, @sourceid)
			end
        end
        
end
go


create trigger FAMILY_ALREADY on FAMILY
instead of insert
as
begin
        declare @idA bigint, @idB bigint, @relationAB bigint, @relationBA bigint, @sourceid bigint
        select @idA = (select friendIdA from inserted)
        select @idB = (select friendIdB from inserted)
        select @relationAB = (select familyRelationAB from inserted)
        select @relationBA = (select familyRelationBA from inserted)
        select @sourceid = (select sourceId from SOURCE_ACCOUNT where added = (select MAX(added) from SOURCE_ACCOUNT))
        if(not exists(select * from FAMILY F where ((F.friendIdA = @idA) AND (F.friendIdB = @idB)) OR ((F.friendIdA = @idB) AND (F.friendIdB = @idA))))
        begin
			insert into FAMILY values(@idA, @idB,@relationAB,@relationBA)
			insert into FAMILY_SOURCE values ((select MAX(familyId) from FAMILY), @sourceid)
        end
        else
        begin
			update FAMILY set familyRelationAB = @relationAB where (((friendIdA = @idA) AND (friendIdB = @idB)) OR ((friendIdA = @idB) AND  (friendIdB = @idA))) AND ((familyRelationAB is null) OR (familyRelationAB = 0)) AND ((@relationAB is not null) OR (@relationAB != 0))
            update FAMILY set familyRelationBA = @relationBA where (((friendIdA = @idA) AND (friendIdB = @idB)) OR ((friendIdA = @idB) AND  (friendIdB = @idA))) AND ((familyRelationBA is null) OR (familyRelationBA = 0)) AND ((@relationBA is not null) OR (@relationBA != 0))
            declare @familyid int = (select familyId from FAMILY F where ((F.friendIdA = @idA) AND (F.friendIdB = @idB)) OR ((F.friendIdA = @idB) AND (F.friendIdB = @idA)))
            if(not exists (select * from FAMILY_SOURCE where familyId = @familyid AND sourceId = @sourceid))
            begin
				insert into FAMILY_SOURCE values(@familyid, @sourceid)
            end             
        end
end
go


/*create trigger INSERTING_EDUCATION on EDUCATION
instead of insert
as
begin
        declare @friendid bigint,
        @schoolid bigint,
        @graduation int
        select @friendid = (select friendId from inserted)
        select @schoolid = (select schoolId from inserted)
        select @graduation = (select graduation from inserted)
        if(not exists(select * from EDUCATION E where ((E.friendId = @friendid) AND (E.schoolId = @schoolid))))
        begin
			insert into EDUCATION values(@friendid, @schoolid, @graduation)
        end
        else
        begin
			update EDUCATION set graduation = @graduation where (friendId = @friendid) AND (schoolId = @schoolid) AND ((graduation = 0) OR (graduation is null))
        end
end
go


create trigger INSERTING_WORK on WORK
instead of insert
as
begin
        declare @friendid bigint,
        @employerid bigint
        select @friendid = (select friendId from inserted)
        select @employerid = (select employerId from inserted)
        if(not exists(select * from WORK W where ((W.friendId = @friendid) AND (W.employerId = @employerid))))
        begin
			insert into WORK values(@friendid, @employerid)
        end
end
go*/


/*create procedure SAVE_EMPLOYMENT
@employername varchar(50),
@employerid bigint,
@friendid bigint
as
begin
        if(exists(select employerId from EMPLOYER where employerId = @employerid))
        begin
			insert into WORK values(@friendid,@employerid)
        end
        else
        begin
			insert into EMPLOYER values(@employerid,@employername)
            insert into WORK values(@friendid,@employerid) 
        end
end
go


create procedure SAVE_EDUCATION
@friendid bigint,
@schoolid bigint,
@schoolname varchar(50), 
@schooltype varchar(20),
@graduation int
as
begin
        if(exists (select schoolId from SCHOOL where schoolId = @schoolid))
        begin
			insert into EDUCATION values(@friendid,@schoolid,@graduation)
        end
        else
        begin
			declare @typeid int
            if(not exists(select typeId from SCHOOL_TYPE where typeName = @schooltype))
            begin
				insert into SCHOOL_TYPE values(@schooltype)
            end
            select @typeid = (select typeId from SCHOOL_TYPE where typeName = @schooltype)
            insert into SCHOOL values(@schoolid, @schoolname,@typeid)
            insert into EDUCATION values(@friendid,@schoolid,@graduation)
        end
end
go*/


/*insert into FAMILY_RELATION values('NULL')
insert into FAMILY_RELATION values('mother')
insert into FAMILY_RELATION values('father')
insert into FAMILY_RELATION values('son')
insert into FAMILY_RELATION values('daughter')
insert into FAMILY_RELATION values('brother')
insert into FAMILY_RELATION values('sister')
insert into FAMILY_RELATION values('cousin')
insert into FAMILY_RELATION values('nephew')
insert into FAMILY_RELATION values('niece')
insert into FAMILY_RELATION values('grandfather')
insert into FAMILY_RELATION values('grandmother')
insert into FAMILY_RELATION values('grandson')
insert into FAMILY_RELATION values('granddaughter')


insert into SCHOOL_TYPE values ('College')
insert into SCHOOL_TYPE values ('High School')*/


/*CREATE LOGIN Tomasz
    WITH PASSWORD = 'Tomasz'
USE FACEBOOK
CREATE USER Tomasz FOR LOGIN Tomasz
grant select, insert, update, delete, execute to Tomasz
GO

CREATE LOGIN Barbara
    WITH PASSWORD = 'Barbara'
USE FACEBOOK
CREATE USER Barbara FOR LOGIN Barbara;
grant select, insert, update, delete, execute to Barbara
GO

CREATE LOGIN Mateusz
    WITH PASSWORD = 'Mateusz'
USE FACEBOOK
CREATE USER Mateusz FOR LOGIN Mateusz
grant select, insert, update, delete, execute to Mateusz
GO*/

 